package com.cn.parks.dao.impl;

import com.cn.parks.dao.UserDao;
import com.cn.parks.entity.User;
import com.cn.parks.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问实现类
 * 实现对用户表的增删改查等基本操作
 */
public class UserDaoImpl implements UserDao {
    
    /**
     * 添加用户
     * @param user 用户对象
     * @return 影响的行数，1表示添加成功，0表示添加失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int insert(User user) throws Exception {
        String sql = "INSERT INTO user (role_id, user_name, real_name, user_pwd, user_phone) VALUES (?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getRoleId());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getRealName());
            pstmt.setString(4, user.getUserPwd());
            pstmt.setString(5, user.getUserPhone());
            return pstmt.executeUpdate();
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 根据ID删除用户
     * @param userId 用户ID
     * @return 影响的行数，1表示删除成功，0表示删除失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int deleteById(String userId) throws Exception {
        String sql = "DELETE FROM user WHERE user_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            return pstmt.executeUpdate();
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 更新用户信息
     * @param user 用户对象
     * @return 影响的行数，1表示更新成功，0表示更新失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int update(User user) throws Exception {
        String sql = "UPDATE user SET role_id=?, user_name=?, real_name=?, user_pwd=?, user_phone=? WHERE user_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getRoleId());
            pstmt.setString(2, user.getUserName());
            pstmt.setString(3, user.getRealName());
            pstmt.setString(4, user.getUserPwd());
            pstmt.setString(5, user.getUserPhone());
            pstmt.setInt(6, user.getUserId());
            return pstmt.executeUpdate();
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 根据ID查询用户
     * @param userId 用户ID
     * @return 用户对象，如果不存在返回null
     * @throws Exception SQL执行异常
     */
    @Override
    public User getById(Integer userId) throws Exception {
        String sql = "SELECT * FROM user WHERE user_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return extractUserFromResultSet(rs);
            }
            return null;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 查询所有用户
     * @return 用户列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<User> getAll() throws Exception {
        String sql = "SELECT * FROM user";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(extractUserFromResultSet(rs));
            }
            return list;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 统计用户数量
     * @return 用户数量
     * @throws Exception SQL执行异常
     */
    @Override
    public Integer getUserCount() throws Exception {
        String sql = "SELECT COUNT(*) FROM user";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
        return 0;
    }

    /**
     * 用户登录验证
     * @param userName 用户名
     * @param password 密码
     * @return 用户对象，如果验证失败返回null
     * @throws Exception SQL执行异常
     */
    @Override
    public User login(String userName, String password) throws Exception {
        String sql = "SELECT * FROM user WHERE user_name = ? AND user_pwd = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return extractUserFromResultSet(rs);
            }
            return null;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 根据角色ID查询用户列表
     * @param roleId 角色ID
     * @return 用户列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<User> getByRoleId(String roleId) throws Exception {
        String sql = "SELECT * FROM user WHERE role_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, roleId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(extractUserFromResultSet(rs));
            }
            return list;
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) DBUtil.closeConnection(conn);
        }
    }

    /**
     * 从ResultSet中提取User对象的辅助方法
     * @param rs ResultSet结果集
     * @return User对象
     * @throws Exception SQL执行异常
     */
    private User extractUserFromResultSet(ResultSet rs) throws Exception {
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setRoleId(rs.getString("role_id"));
        user.setUserName(rs.getString("user_name"));
        user.setRealName(rs.getString("real_name"));
        user.setUserPwd(rs.getString("user_pwd"));
        user.setUserPhone(rs.getString("user_phone"));
        return user;
    }
} 